﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Data.Common;
using System.Data;
namespace DAO
{
    public class NhanVienDAO
    {
        public List<NhanVienDTO> LayDSNhanVien()
        {
            string sql = "SELECT MaNV,HoTen,NgaySinh,GioiTinh,DiaChi,DienThoai,Diem,HeSo FROM NhanVien";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<NhanVienDTO> danhSach = new List<NhanVienDTO>();
            while (reader.Read())
            {
                NhanVienDTO nvDTO = new NhanVienDTO();
                nvDTO.MaNV = reader.GetInt32(0);
                nvDTO.HoTen = reader.GetString(1);
                nvDTO.NgaySinh = reader.GetDateTime(2); 
                nvDTO.GioiTinh = reader.GetString(3);
                nvDTO.DiaChi = reader.GetString(4);
                nvDTO.DienThoai = reader.GetString(5);
                var nvdiem=reader.GetValue(6);
                nvDTO.Diem = nvdiem == DBNull.Value ? 0 : (int)nvdiem;
                nvDTO.HeSo = reader.GetInt32(7);
                danhSach.Add(nvDTO);
            }
            conn.Close();
            return danhSach;
        }
        //----------------------------------------------------------------------
        public int ThemNhanVien(NhanVienDTO nvDTO)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO NhanVien(HoTen,NgaySinh,GioiTinh,DiaChi,DienThoai,Diem,HeSo) VALUES(@HoTen,@NgaySinh,@GioiTinh,@DiaChi,@DienThoai,@Diem,@HeSo)";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@HoTen", OleDbType.VarChar);
            para.Value = nvDTO.HoTen;
            para = cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            para.Value = nvDTO.NgaySinh;
            para = cmd.Parameters.Add("@GioiTinh", OleDbType.VarChar);
            para.Value = nvDTO.GioiTinh;
            para = cmd.Parameters.Add("@DiaChi", OleDbType.VarChar);
            para.Value = nvDTO.DiaChi;
            para = cmd.Parameters.Add("@DienThoai", OleDbType.VarChar);
            para.Value = nvDTO.DienThoai;
            para = cmd.Parameters.Add("@Diem", OleDbType.Integer);
            para.Value = nvDTO.Diem;
            para = cmd.Parameters.Add("@HeSo", OleDbType.Integer);
            para.Value = nvDTO.HeSo;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;

        }
        //---------------------------------------------------------------------------------
        public int XoaNhanVien(int MaNV)
        {
            string sql = "delete from NhanVien where MaNV=" + MaNV.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            // conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        //----------------------------------------------------------------------------------
        public int UpdateNV(NhanVienDTO nvDTO)
        {
            //HoTen,NgaySinh,GioiTinh,DiaChi,DienThoai,Diem,HeSo
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "UPDATE NhanVien set HoTen=@HoTen,NgaySinh=@NgaySinh,GioiTinh=@GioiTinh,DiaChi=@DiaChi,DienThoai=@DienThoai,Diem=@Diem,HeSo=@HeSo where MaNV=" + nvDTO.MaNV.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@HoTen", OleDbType.VarChar);
            para.Value = nvDTO.HoTen;
            para = cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
            para.Value = nvDTO.NgaySinh;
            para = cmd.Parameters.Add("@GioiTinh", OleDbType.VarChar);
            para.Value = nvDTO.GioiTinh;
            para = cmd.Parameters.Add("@DiaChi", OleDbType.VarChar);
            para.Value = nvDTO.DiaChi;
            para = cmd.Parameters.Add("@DienThoai", OleDbType.VarChar);
            para.Value = nvDTO.DienThoai;
            para = cmd.Parameters.Add("@Diem", OleDbType.Integer);
            para.Value = nvDTO.Diem;
            para = cmd.Parameters.Add("@HeSo", OleDbType.Integer);
            para.Value = nvDTO.HeSo;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int UpdateNV(int MaNV, int cv)
        {
            OleDbConnection conn = DAO.DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "update NhanVien set Diem=Diem+@cv where MaNV=@MaNV";
            cmd.Connection = conn;
            OleDbParameter pa;
            pa = new OleDbParameter("@cv", OleDbType.Integer);
            pa.Value = cv;
            cmd.Parameters.Add(pa);
            pa = new OleDbParameter("@MaNV", OleDbType.Integer);
            pa.Value = MaNV;
            cmd.Parameters.Add(pa);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        //---------------------------------------------------------------------------
        public List<NhanVienDTO> CTNhanVien(int MaNV)
        {
            string sql = "SELECT * from NhanVien where MaNV=" + MaNV.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<NhanVienDTO> danhSach = new List<NhanVienDTO>();            
            while (reader.Read())
            {
                NhanVienDTO nvDTO = new NhanVienDTO();
                nvDTO.MaNV = reader.GetInt32(0);
                nvDTO.HoTen = reader.GetString(1);
                nvDTO.NgaySinh = reader.GetDateTime(2);
                nvDTO.GioiTinh = reader.GetString(3);
                nvDTO.DiaChi = reader.GetString(4);
                nvDTO.DienThoai = reader.GetString(5);
                var nvdiem = reader.GetValue(6);
                nvDTO.Diem = nvdiem == DBNull.Value ? 0 : (int)nvdiem;
                nvDTO.HeSo = reader.GetInt32(7);
                danhSach.Add(nvDTO);
            }
            conn.Close();
            return danhSach;
        }
        //-----------------------------------------------------------------------------
        public List<NhanVienDTO> TimNhanVien(string TenNV)
        {
            string sql = "SELECT * from NhanVien where HoTen LIKE '%" + TenNV+"%' or MaNV LIKE '%" + TenNV+"%'";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<NhanVienDTO> danhSach = new List<NhanVienDTO>();
            while (reader.Read())
            {
                NhanVienDTO nvDTO = new NhanVienDTO();
                nvDTO.MaNV = reader.GetInt32(0);
                nvDTO.HoTen = reader.GetString(1);
                nvDTO.NgaySinh = reader.GetDateTime(2);
                nvDTO.GioiTinh = reader.GetString(3);
                nvDTO.DiaChi = reader.GetString(4);
                nvDTO.DienThoai = reader.GetString(5);
                var nvdiem = reader.GetValue(6);
                nvDTO.Diem = nvdiem == DBNull.Value ? 0 : (int)nvdiem;
                nvDTO.HeSo = reader.GetInt32(7);
                danhSach.Add(nvDTO);
            }
            conn.Close();
            return danhSach;
        }
        public DataTable LayMaVaTen()
        {
            string sql = "select MaNV,HoTen from NhanVien";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            conn.Close();
            return ds.Tables[0];
        }
        public string LayTenNV(int maNV)
        {
            string sql = " SELECT HoTen FROM NhanVien WHERE MaNV = " + maNV;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            string kq = (string)cmd.ExecuteScalar();
            return kq;
        }
    }
}
